﻿
CREATE PROCEDURE [dbo].[balcon_UpdateSolduriInitialeJurnale]
	@CodUnitate smallint, 
	@Cont dbo.TipCont,
	@IDJurnal int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--pentru cazul in care se sterg toate analiticele se pune pe zero
	UPDATE cnt SET cnt.DebitInitial = 0,cnt.CreditInitial = 0
	FROM Conturi cnt
	WHERE cnt.Cont = @Cont AND cnt.CodUnitate = @CodUnitate

	--modifica contul suma din tabela conturi

	UPDATE cnt SET cnt.DebitInitial = 
	CASE 
		WHEN Tip='A' THEN jsip.DebitInitial 
		WHEN Tip='P' THEN 0 
		WHEN Tip='B' AND jsip.DebitInitial-jsip.CreditInitial>0 THEN jsip.DebitInitial-jsip.CreditInitial 
		WHEN Tip='B' AND jsip.DebitInitial-jsip.CreditInitial<0 THEN 0
		ELSE 0
	END,
	cnt.CreditInitial = 
	CASE 
		WHEN Tip='A' THEN 0 
		WHEN Tip='P' THEN jsip.CreditInitial
		WHEN Tip='B' AND jsip.DebitInitial-jsip.CreditInitial>0 THEN 0 
		WHEN Tip='B' AND jsip.DebitInitial-jsip.CreditInitial<0 THEN jsip.CreditInitial - jsip.DebitInitial
		ELSE 0
	END
	FROM Conturi cnt, 
	(SELECT COALESCE(SUM(a.DebitInitial),0) DebitInitial, COALESCE(SUM(a.CreditInital),0) CreditInitial, Cont, CodUnitate FROM JurnaleSolduriInitiale a 
	 WHERE a.Cont = @Cont AND CodUnitate = @CodUnitate 
	 GROUP BY Cont, CodUnitate) jsip
	WHERE cnt.Cont = @Cont AND cnt.CodUnitate = @CodUnitate 

	SELECT @Cont = ContParinte FROM Conturi WHERE CodUnitate = @CodUnitate AND Cont = @Cont
	
	WHILE @Cont <> 'N/A'
	BEGIN
		--adauga parintele pentru jurnalul respectiv
		--IF NOT EXISTS(SELECT * FROM JurnaleSolduriInitiale a WHERE a.Cont=@Cont AND a.IDJurnal=@IDJurnal AND a.CodUnitate=@CodUnitate)
			--INSERT INTO JurnaleSolduriInitiale ([IDJurnal],[Cont],[CodUnitate],[DebitInitial],[CreditInital],[DebitPrecedent],[CreditPrecedent])
			--VALUES (@IDJurnal,@Cont,@CodUnitate,0,0,0,0)
	
	    --pune pe zero in cazul stergerii
		UPDATE jsi SET jsi.DebitInitial = 0,jsi.CreditInital = 0
		FROM JurnaleSolduriInitiale jsi
		WHERE jsi.Cont = @Cont AND jsi.CodUnitate = @CodUnitate AND jsi.IDJurnal = @IDJurnal
		
		--modifica soldul initial al contului parinte din solduri initiale jurnale
		UPDATE jsi SET jsi.DebitInitial = jsip.DebitInitial,jsi.CreditInital = jsip.CreditInitial
		FROM JurnaleSolduriInitiale jsi, 
		(SELECT COALESCE(SUM(a.DebitInitial),0) DebitInitial, COALESCE(SUM(a.CreditInital),0) CreditInitial, ContParinte, a.CodUnitate,a.IDJurnal  FROM JurnaleSolduriInitiale a 
		 JOIN Conturi c ON a.Cont = c.Cont AND a.CodUnitate = c.CodUnitate
		 WHERE c.ContParinte = @Cont AND a.CodUnitate = @CodUnitate AND a.IDJurnal=@IDJurnal
		 GROUP BY c.ContParinte, a.CodUnitate,a.IDJurnal ) jsip
		WHERE jsi.Cont = @Cont AND jsi.CodUnitate = @CodUnitate AND jsi.IDJurnal = jsip.IDJurnal
	
		--pentru cazul in care se sterg toate analiticele se pune pe zero
		UPDATE cnt SET cnt.DebitInitial = 0,cnt.CreditInitial = 0
		FROM Conturi cnt
		WHERE cnt.Cont = @Cont AND cnt.CodUnitate = @CodUnitate

		--modifica soldurile initiale parinte din tabela conturi
		UPDATE cnt SET
		 cnt.DebitInitial = 
			CASE 
				WHEN Tip='A' THEN jsip.DebitInitial 
				WHEN Tip='P' THEN 0 
				WHEN Tip='B' AND jsip.DebitInitial-jsip.CreditInitial>0 THEN jsip.DebitInitial-jsip.CreditInitial 
				WHEN Tip='B' AND jsip.DebitInitial-jsip.CreditInitial<0 THEN 0
				ELSE 0
			END,
			cnt.CreditInitial = 
			CASE 
				WHEN Tip='A' THEN 0 
				WHEN Tip='P' THEN jsip.CreditInitial
				WHEN Tip='B' AND jsip.DebitInitial-jsip.CreditInitial>0 THEN 0 
				WHEN Tip='B' AND jsip.DebitInitial-jsip.CreditInitial<0 THEN jsip.CreditInitial - jsip.DebitInitial
				ELSE 0
			END
		FROM Conturi cnt, 
		(SELECT COALESCE(SUM(DebitInitial),0) DebitInitial, COALESCE(SUM(CreditInitial),0) CreditInitial, ContParinte,CodUnitate FROM Conturi c 
		 WHERE ContParinte = @Cont AND CodUnitate = @CodUnitate
		 GROUP BY ContParinte,CodUnitate) jsip
		WHERE cnt.Cont = @Cont AND cnt.CodUnitate = @CodUnitate
		
		
		SELECT @Cont = ContParinte FROM Conturi WHERE CodUnitate = @CodUnitate AND Cont = @Cont
		
	END
	

END